package com.xinchuang.sqlite;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import com.xinchuang.entity.elseParam.Ele;
import com.xinchuang.sqlite.entity.*;
import com.xinchuang.util.AreaUtil;
import com.xinchuang.util.FileElseUtil;
import com.xinchuang.util.SnowflakeIdWorker;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName TargetRepo
 * @Description: TODO(Sqlite操作)
 * @Author zhangxuan
 * @Date 2021-01-06 17:15
 * @Verion V1.0
 */
@Slf4j
public class TargetRepo {

    /**
     * 新增目标
     */
    private static final String ADD_TARGET_SQL = "INSERT OR IGNORE INTO target_table (id,key,key_name,key_type,key_value,remark,person,source,insert_time,update_time,upload_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 新增子目标基本信息
     */
    private static final String ADD_SUB_TARGET_SQL = "INSERT OR IGNORE INTO target_sub_table (id,child_id,key,key_name,key_type,key_value,remark,person,source,insert_time,update_time,upload_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 新增子目标详细信息
     */
    private static final String ADD_SUB_TARGET_ELSE_SQL = "INSERT OR IGNORE INTO target_sub_else_table (id,child_id,key,key_name,key_type,key_value,remark,person,source,insert_time,update_time,upload_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 删除单个子目标详细信息
     */
    private static final String DELETE_SUB_TARGET_ELSE_SQL = "DELETE FROM target_sub_else_table WHERE child_id = ?";

    /**
     * 新增目标其他信息
     */
    private static final String ADD_TARGET_ELSE_SQL = "INSERT OR IGNORE INTO target_else_table (id,achievement_id,key,key_name,key_type,key_value,remark,person,source,insert_time,update_time,upload_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 新增目标专有信息
     */
    private static final String ADD_TARGET_FUNCTION_SQL = "INSERT OR IGNORE INTO target_function_table (id,achievement_id,key,key_name,key_type,key_value,remark,person,source,insert_time,update_time,upload_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 新增子目标信息
     */
    private static final String ADD_TARGET_SUB_SQL = "INSERT OR IGNORE INTO target_sub_table (id,child_id,key,key_name,key_type,key_value,remark,person,source,insert_time,update_time,upload_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 新增目标与子目标关系
     */
    private static final String ADD_TARGET_SUB_RELATION_SQL = "INSERT OR IGNORE INTO target_relation_table (id,target_id,child_name,child_num,version,remark) VALUES (?,?,?,?,?,?)";

    /**
     * 新增版本信息
     */
    private static final String ADD_VERION_SQL = "INSERT OR IGNORE INTO version_table (id,target_number,version,publish_num,made_time,achievement_type,keyword,unit,remark,insert_time,delete_time,data_state) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 查询目标信息
     */
    private static final String SELECT_TARGET_SQL = "SELECT id,key,key_name,key_type,key_value,remark,person,source FROM target_table WHERE data_state = '1'";

    /**
     * 更新目标信息
     */
    private static final String UPDATE_TARGET_SQL = "UPDATE target_table SET key = ?,key_name = ?,key_type = ?,key_value = ?,remark = ?,person = ?,source = ?,update_time = ? WHERE id = ?";

    /**
     * 删除目标
     */
    private static final String DELETE_TARGET_SQL = "UPDATE target_table SET delete_time = ?,data_state = '0' WHERE id = ?";

    /**
     * 查询目标其他信息
     */
    private static final String SELECT_TARGET_ELSE_SQL = "SELECT id,achievement_id,key,key_name,key_type,key_value,remark,person,source FROM target_else_table WHERE data_state = '1'";

    /**
     * 查询目标其他信息
     */
    private static final String SELECT_TARGET_FUNCTION_SQL = "SELECT id,achievement_id,key,key_name,key_type,key_value,remark,person,source FROM target_function_table WHERE data_state = '1'";

    /**
     * 查询目标其他信息
     */
    private static final String SELECT_TARGET_ELSE_AUTHOR_SQL = "SELECT id,achievement_id,key,key_name,key_type,key_value,remark,person,source FROM target_else_table WHERE person = ?";

    /**
     * 更新目标其他信息
     */
    private static final String UPDATE_TARGET_ELSE_SQL = "UPDATE target_else_table SET key = ?,key_name = ?,key_type = ?,key_value = ?,remark = ?,person = ?,source = ?,update_time = ? WHERE id = ?";

    /**
     * 更新目标专有信息
     */
    private static final String UPDATE_TARGET_FUNCTION_SQL = "UPDATE target_function_table SET key = ?,key_name = ?,key_type = ?,key_value = ?,remark = ?,person = ?,source = ?,update_time = ? WHERE id = ?";

    /**
     * 删除目标其他信息
     */
    private static final String DELETE_TARGET_ELSE_SQL = "UPDATE target_else_table SET delete_time = ?,data_state = '0' WHERE id = ?";

    /**
     * 查询所有子目标信息
     */
    private static final String SELECT_TARGET_SUB_SQL = "SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_table WHERE data_state = '1' ORDER BY child_id,insert_time DESC";

    /**
     * 查询不是预生成的子目标信息
     */
    private static final String SELECT_TARGET_SUB_NO_PRE_SQL = "SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_table WHERE data_state = '1' AND remark != 'pre' ORDER BY child_id,insert_time DESC";


    /**
     * 查询单个子目标信息
     */
    private static final String SELECT_TARGET_SUB_SINGLE_SQL = "SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_table WHERE data_state = '1' and child_id = ?";

    /**
     * 查询单个子目标详细信息
     */
    private static final String SELECT_TARGET_SUB_ELSE_SINGLE_SQL = "SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_else_table WHERE data_state = '1' and child_id = ?";

    /**
     * 查询单个子目标某个key的值
     */
    private static final String SELECT_TARGET_SUB_KEY_VALUE = "SELECT key_value FROM target_sub_table WHERE data_state = '1' and child_id = ? AND key = ?";

    /**
     * 更新子目标信息
     */
    private static final String UPDATE_TARGET_SUB_SQL = "UPDATE target_sub_table SET key = ?,key_name = ?,key_type = ?,key_value = ?,remark = ?,person = ?,source = ?,update_time = ? WHERE id = ?";

    /**
     * 删除单个子目标信息
     */
    private static final String DELETE_TARGET_SUB_SQL = "DELETE FROM target_sub_table WHERE child_id = ? AND key NOT IN ('icon')";

    /**
     * 删除单个子目标信息
     */
    private static final String DELETE_TARGET_ALL_SUB_SQL = "DELETE FROM target_sub_table WHERE child_id = ?";

    /**
     * 清理子目标
     */
    private static final String CLEAR_TARGET_SUB_SQL = "DELETE FROM target_sub_table";

    /**
     * 查询子目标列表
     */
    private static final String SELECT_TARGET_SUB_LIST_SQL = "SELECT child_id,key,key_value FROM target_sub_table WHERE key = ?";

    /**
     * 查询关系数量
     */
    private static final String SElECT_RELATION_COUNT_SQL = "SELECT COUNT(1) FROM target_relation_table";

    /**
     * 查询关系信息
     */
    private static final String SELECT_TARGET_SUB_RELATION_SQL = "SELECT id,target_id,child_name,child_num,version,remark FROM target_relation_table";

    /**
     * 删除关系信息
     */
    private static final String DELETE_TARGET_SUB_RELATION_SQL = "DELETE FROM target_relation_table WHERE id = '1'";

    /**
     * 查询所有版本信息
     */
    private static final String SELECT_VERSION_SQL = "SELECT id,target_number,version,publish_num,made_time,achievement_type,keyword,unit,remark FROM version_table WHERE data_state = '1'";

    /**
     * 查询单个版本信息
     */
    private static final String SELECT_VERSION_SINGLE_SQL = "SELECT id,target_number,version,publish_num,made_time,achievement_type,keyword,unit,remark FROM version_table WHERE id = ? and data_state = '1'";

    /**
     * 删除版本信息
     */
    private static final String DELETE_VERSION_SQL = "UPDATE version_table SET delete_time = ?,data_state = '0' WHERE id = ?";

    /**
     * 子目标重名
     */
    private static final String SELECT_SUB_NAME = "SELECT child_id FROM target_sub_table WHERE key = 'targetName' AND key_value = ? AND remark NOT IN ('pre')";

    private static final String UPDATE_MAIN_KEY_VALUE_BY_KEY = "UPDATE target_table SET key_value = ? WHERE key = ?";

    private static final String UPDATE_SUB_KEY_VALUE_BY_KEY = "UPDATE target_sub_table SET key_value = ? WHERE key = ? AND child_id = ?";

    /**
     * 查询子目标信息数量
     */
    private final String SELECT_SUB_COUNT_SQL = "SELECT COUNT(1) FROM target_sub_table WHERE child_id = ?";

    private SnowflakeIdWorker snow = new SnowflakeIdWorker(1, 1);

    /**
     *@author zhangxuan
     *@Description TODO 插入主目标参数
     *@Date 10:14 2021/1/7
     *@Param [sqliteUrl, targetData]
     *@return int
     **/
    public synchronized int addTargetList(String sqliteUrl, List<TargetData> targetData) throws SQLException {
        Connection connection = DbConnManager.getDbConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("DELETE FROM target_table");
            ps.execute();
            ps = connection.prepareStatement(ADD_TARGET_SQL);
            for (TargetData data : targetData) {
                ps.setString(1, data.getId());
                ps.setString(2, data.getKey());
                ps.setString(3, data.getKeyName());
                ps.setString(4, data.getKeyType());
                ps.setString(5, data.getKeyValue());
                ps.setString(6, data.getRemark());
                ps.setString(7, data.getPerson());
                ps.setString(8, data.getSource());
                ps.setLong(9, System.currentTimeMillis());
                ps.setLong(10 , 0L);
                ps.setLong(11 , 0L);
                ps.setLong(12 , 0L);
                ps.setLong(13, 1L);
                ps.addBatch();
            }
            return ps.executeBatch().length;
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 更新目标信息
     *@Date 10:44 2021/1/11
     *@Param [sqliteUrl, targetData]
     *@return int
     **/
    public synchronized int updateTarget(String sqliteUrl, List<TargetData> targetData) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SQL);
            ResultSet resultSet = ps.executeQuery();
            List<TargetData> updateList = new ArrayList<>();
            while (resultSet.next()) {
                String key = resultSet.getString(2);
                for (int i = 0; i < targetData.size(); i++) {
                    TargetData data =  targetData.get(i);
                    if (key.equals(data.getKey())) {
                        data.setId(resultSet.getString(1));
                        updateList.add(data);
                        targetData.remove(data);
                        break;
                    }
                }
            }
            resultSet.close();
            ps.close();
            if (!updateList.isEmpty()) {
                ps = connection.prepareStatement(UPDATE_TARGET_SQL);
                for (TargetData data : updateList) {
                    ps.setString(1, data.getKey());
                    ps.setString(2, data.getKeyName());
                    ps.setString(3, data.getKeyType());
                    ps.setString(4, data.getKeyValue());
                    ps.setString(5, data.getRemark());
                    ps.setString(6, data.getPerson());
                    ps.setString(7, data.getSource());
                    ps.setLong(8, System.currentTimeMillis());
                    ps.setString(9, data.getId());
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            if (!targetData.isEmpty()) {
                ps = connection.prepareStatement(ADD_TARGET_SQL);
                for (TargetData data : targetData) {
                    ps.setLong(1, snow.nextId());
                    ps.setString(2, data.getKey());
                    ps.setString(3, data.getKeyName());
                    ps.setString(4, data.getKeyType());
                    ps.setString(5, data.getKeyValue());
                    ps.setString(6, data.getRemark());
                    ps.setString(7, data.getPerson());
                    ps.setString(8, data.getSource());
                    ps.setLong(9, System.currentTimeMillis());
                    ps.setLong(10 , 0L);
                    ps.setLong(11 , 0L);
                    ps.setLong(12 , 0L);
                    ps.setLong(13, 1L);
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            connection.commit();
        } catch (SQLException e){
            log.error(e.getMessage(), e);
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
            return 0;
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return 1;
    }

    /**
     *@author zhangxuan
     *@Description TODO 更新目标其他信息
     *@Date 11:02 2021/1/12
     *@Param [sqliteUrl, list, targetId, author]
     *@return int
     **/
    public synchronized int updateTargetElse(String sqliteUrl, List<TargetElseData> list, String targetId, String taskId) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
            ResultSet resultSet = ps.executeQuery();
            List<TargetElseData> updateList = new ArrayList<>();
            while (resultSet.next()) {
                String key = resultSet.getString(3);
//                String author = resultSet.getString(8);
                for (int i = 0; i < list.size(); i++) {
                    TargetElseData data =  list.get(i);
//                    if (taskId.equals(author) && key.equals(data.getKey())) {
                    if (key.equals(data.getKey())) {
                        data.setId(resultSet.getString(1));
                        updateList.add(data);
                        list.remove(data);
                        break;
                    }
                }
            }
            resultSet.close();
            ps.close();
            if (!updateList.isEmpty()) {
                ps = connection.prepareStatement(UPDATE_TARGET_ELSE_SQL);
                for (TargetElseData data : updateList) {
                    ps.setString(1, data.getKey());
                    ps.setString(2, data.getKeyName());
                    ps.setString(3, data.getKeyType());
                    ps.setString(4, data.getKeyValue());
                    ps.setString(5, data.getRemark());
                    ps.setString(6, data.getPerson());
                    ps.setString(7, data.getSource());
                    ps.setLong(8, System.currentTimeMillis());
                    ps.setString(9, data.getId());
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            if (!list.isEmpty()) {
                ps = connection.prepareStatement(ADD_TARGET_ELSE_SQL);
                for (TargetElseData data : list) {
                    ps.setLong(1, snow.nextId());
                    ps.setString(2, targetId);
                    ps.setString(3, data.getKey());
                    ps.setString(4, data.getKeyName());
                    ps.setString(5, data.getKeyType());
                    ps.setString(6, data.getKeyValue());
                    ps.setString(7, data.getRemark());
                    ps.setString(8, taskId);
                    ps.setString(9, "WEB");
                    ps.setLong(10, System.currentTimeMillis());
                    ps.setLong(11 , 0L);
                    ps.setLong(12 , 0L);
                    ps.setLong(13 , 0L);
                    ps.setLong(14, 1L);
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            connection.commit();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return 0;
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return 1;
    }

    /**
     *@author zhangxuan
     *@Description TODO 主表使用更新目标其他信息
     *@Date 10:01 2021/4/12
     *@Param [sqliteUrl, list, targetId, taskId]
     *@return int
     **/
    public synchronized int updateTargetElseMain(String sqliteUrl, List<TargetData> list, String targetId, String taskId) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
            ResultSet resultSet = ps.executeQuery();
            List<TargetData> updateList = new ArrayList<>();
            while (resultSet.next()) {
                String key = resultSet.getString(3);
//                String author = resultSet.getString(8);
                for (int i = 0; i < list.size(); i++) {
                    TargetData data =  list.get(i);
//                    if (taskId.equals(author) && key.equals(data.getKey())) {
                    if (key.equals(data.getKey())) {
                        data.setId(resultSet.getString(1));
                        updateList.add(data);
                        list.remove(data);
                        break;
                    }
                }
            }
            resultSet.close();
            ps.close();
            if (!updateList.isEmpty()) {
                ps = connection.prepareStatement(UPDATE_TARGET_ELSE_SQL);
                for (TargetData data : updateList) {
                    ps.setString(1, data.getKey());
                    ps.setString(2, data.getKeyName());
                    ps.setString(3, data.getKeyType());
                    ps.setString(4, data.getKeyValue());
                    ps.setString(5, data.getRemark());
                    ps.setString(6, data.getPerson());
                    ps.setString(7, data.getSource());
                    ps.setLong(8, System.currentTimeMillis());
                    ps.setString(9, data.getId());
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            if (!list.isEmpty()) {
                ps = connection.prepareStatement(ADD_TARGET_ELSE_SQL);
                for (TargetData data : list) {
                    ps.setLong(1, snow.nextId());
                    ps.setString(2, targetId);
                    ps.setString(3, data.getKey());
                    ps.setString(4, data.getKeyName());
                    ps.setString(5, data.getKeyType());
                    ps.setString(6, data.getKeyValue());
                    ps.setString(7, data.getRemark());
                    ps.setString(8, taskId);
                    ps.setString(9, "WEB");
                    ps.setLong(10, System.currentTimeMillis());
                    ps.setLong(11 , 0L);
                    ps.setLong(12 , 0L);
                    ps.setLong(13 , 0L);
                    ps.setLong(14, 1L);
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            connection.commit();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return 0;
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return 1;
    }

    /**
     *@author zhangxuan
     *@Description TODO 主表使用更新目标专有信息
     *@Date 10:01 2021/4/12
     *@Param [sqliteUrl, list, targetId, taskId]
     *@return int
     **/
    public synchronized int updateTargetFunctionMain(String sqliteUrl, List<TargetData> list, String targetId, String taskId) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_FUNCTION_SQL);
            ResultSet resultSet = ps.executeQuery();
            List<TargetData> updateList = new ArrayList<>();
            while (resultSet.next()) {
                String key = resultSet.getString(3);
                String author = resultSet.getString(8);
                for (int i = 0; i < list.size(); i++) {
                    TargetData data =  list.get(i);
                    if (taskId.equals(author) && key.equals(data.getKey())) {
                        data.setId(resultSet.getString(1));
                        updateList.add(data);
                        list.remove(data);
                        break;
                    }
                }
            }
            resultSet.close();
            ps.close();
            if (!updateList.isEmpty()) {
                ps = connection.prepareStatement(UPDATE_TARGET_FUNCTION_SQL);
                for (TargetData data : updateList) {
                    ps.setString(1, data.getKey());
                    ps.setString(2, data.getKeyName());
                    ps.setString(3, data.getKeyType());
                    ps.setString(4, data.getKeyValue());
                    ps.setString(5, data.getRemark());
                    ps.setString(6, data.getPerson());
                    ps.setString(7, data.getSource());
                    ps.setLong(8, System.currentTimeMillis());
                    ps.setString(9, data.getId());
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            if (!list.isEmpty()) {
                ps = connection.prepareStatement(ADD_TARGET_FUNCTION_SQL);
                for (TargetData data : list) {
                    ps.setLong(1, snow.nextId());
                    ps.setString(2, targetId);
                    ps.setString(3, data.getKey());
                    ps.setString(4, data.getKeyName());
                    ps.setString(5, data.getKeyType());
                    ps.setString(6, data.getKeyValue());
                    ps.setString(7, data.getRemark());
                    ps.setString(8, taskId);
                    ps.setString(9, "WEB");
                    ps.setLong(10, System.currentTimeMillis());
                    ps.setLong(11 , 0L);
                    ps.setLong(12 , 0L);
                    ps.setLong(13 , 0L);
                    ps.setLong(14, 1L);
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            connection.commit();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return 0;
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return 1;
    }

    /**
     *@author zhangxuan
     *@Description TODO 更新目标详细信息
     *@Date 11:02 2021/1/12
     *@Param [sqliteUrl, list, targetId, author]
     *@return int
     **/
    public synchronized int updateTargetSpecialData(String sqliteUrl, List<TargetData> list, String targetId, String taskId) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
            ResultSet resultSet = ps.executeQuery();
            List<TargetData> updateList = new ArrayList<>();
            while (resultSet.next()) {
                String key = resultSet.getString(3);
                String author = resultSet.getString(8);
                for (int i = 0; i < list.size(); i++) {
                    TargetData data =  list.get(i);
                    if (key.equals(data.getKey())) {
                        data.setId(resultSet.getString(1));
                        updateList.add(data);
                        list.remove(data);
                        break;
                    }
                }
            }
            resultSet.close();
            ps.close();
            if (!updateList.isEmpty()) {
                ps = connection.prepareStatement(UPDATE_TARGET_ELSE_SQL);
                for (TargetData data : updateList) {
                    ps.setString(1, data.getKey());
                    ps.setString(2, data.getKeyName());
                    ps.setString(3, data.getKeyType());
                    ps.setString(4, data.getKeyValue());
                    ps.setString(5, data.getRemark());
                    ps.setString(6, data.getPerson());
                    ps.setString(7, data.getSource());
                    ps.setLong(8, System.currentTimeMillis());
                    ps.setString(9, data.getId());
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            if (!list.isEmpty()) {
                ps = connection.prepareStatement(ADD_TARGET_ELSE_SQL);
                for (TargetData data : list) {
                    ps.setLong(1, snow.nextId());
                    ps.setString(2, targetId);
                    ps.setString(3, data.getKey());
                    ps.setString(4, data.getKeyName());
                    ps.setString(5, data.getKeyType());
                    ps.setString(6, data.getKeyValue());
                    ps.setString(7, data.getRemark());
                    ps.setString(8, taskId);
                    ps.setString(9, "WEB");
                    ps.setLong(10, System.currentTimeMillis());
                    ps.setLong(11 , 0L);
                    ps.setLong(12 , 0L);
                    ps.setLong(13 , 0L);
                    ps.setLong(14, 1L);
                    ps.addBatch();
                }
                ps.executeBatch();
                ps.close();
            }
            connection.commit();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return 0;
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return 1;
    }

    public String getSubtaskKeyValue(String sqliteUrl, String subId, String key) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return null;
        }
        try {
            String str = null;
            PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_KEY_VALUE);
            ps.setString(1, subId);
            ps.setString(2, key);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                str = resultSet.getString("key_value");
            }
            resultSet.close();
            ps.close();
            return str;
        } catch (Exception e) {
            return "0";
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
    }

    /**
     * @return int
     * @author zhangxuan
     * @Description TODO 新增子目标
     * @Date 14:13 2021/1/12
     * @Param [sqliteUrl, list, author]
     **/
    public synchronized String addTargetSubMessage(String sqliteUrl, String subId, List<TargetData> list, String author) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return "0";
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = null;
            if (!subId.equals("0")) {
                ps = connection.prepareStatement(DELETE_TARGET_SUB_SQL);
                ps.setString(1, subId);
                ps.execute();
                ps.close();
            }
            ps = connection.prepareStatement(ADD_TARGET_SUB_SQL);
            if (StringUtils.isBlank(subId) || subId.equals("0")) {
                subId = String.valueOf(snow.nextId());
            }
            Map<String, Object> map = new HashMap<>();
            for (TargetData data : list) {
                ps.setLong(1, snow.nextId());
                ps.setString(2, subId);
                ps.setString(3, data.getKey());
                ps.setString(4, data.getKeyName());
                ps.setString(5, data.getKeyType());
                ps.setString(6, data.getKeyValue());
                ps.setString(7, StringUtils.isNotBlank(data.getRemark()) ? data.getRemark() : "");
                ps.setString(8, author);
                ps.setString(9, "WEB");
                ps.setLong(10, System.currentTimeMillis());
                ps.setLong(11, 0L);
                ps.setLong(12, 0L);
                ps.setLong(13, 0L);
                ps.setLong(14, 1L);
                ps.addBatch();
            }
            ps.executeBatch();
            connection.commit();
            ps.close();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return "0";
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return subId;
    }

    public String selectSubName(String sqliteUrl, String name) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return null;
        }
        String id = null;
        try {
            PreparedStatement ps = connection.prepareStatement(SELECT_SUB_NAME);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                id = rs.getString("child_id");
            }
            rs.close();
            ps.close();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return "0";
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return id;
    }

    /**
     *@author zhangxuan
     *@Description TODO 新增子目标
     *@Date 14:13 2021/1/12
     *@Param [sqliteUrl, list, author]
     *@return int
     **/
    public synchronized String addTargetSub(String sqliteUrl, String subId, List<TargetSubData> list, String author) {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return "0";
        }
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = null;
            if (subId.equals("0")) {
                String subName = null;
                for (TargetSubData targetSubData : list) {
                    if (targetSubData.getKey().equals("subName")) {
                        subName = targetSubData.getKeyValue();
                        break;
                    }
                }
                if (StringUtils.isNotBlank(subName)) {
                    ps = connection.prepareStatement(SELECT_TARGET_SUB_LIST_SQL);
                    ps.setString(1 , "subName");
                    ResultSet resultSet = ps.executeQuery();
                    while (resultSet.next()) {
                        if (resultSet.getString(3).equals(subName)) {
                            resultSet.close();
                            ps.close();
                            return "2";
                        }
                    }
                }
            }
            if (!subId.equals("0")) {
                ps = connection.prepareStatement(DELETE_TARGET_SUB_SQL);
                ps.setString(1, subId);
                ps.execute();
                ps.close();
            }
            ps = connection.prepareStatement(ADD_TARGET_SUB_SQL);
            if (subId.equals("0")) {
                subId = String.valueOf(snow.nextId());
            }
            String[] strings = {"shape", "height", "length", "width", "angle", "radius", "innerRadius", "outRadius", "lon", "lat", "earthHeight"};
            Map<String, Object> map = new HashMap<>();
            for (TargetSubData data : list) {
                ps.setLong(1, snow.nextId());
                ps.setString(2, subId);
                ps.setString(3, data.getKey());
                for (String string : strings) {
                    if (string.equals(data.getKey())) {
                        map.put(string, data.getKeyValue());
                        break;
                    }
                }
                ps.setString(4, data.getKeyName());
                ps.setString(5, data.getKeyType());
                ps.setString(6, data.getKeyValue());
                ps.setString(7, data.getRemark());
                ps.setString(8, author);
                ps.setString(9, "WEB");
                ps.setLong(10, System.currentTimeMillis());
                ps.setLong(11 , 0L);
                ps.setLong(12 , 0L);
                ps.setLong(13 , 0L);
                ps.setLong(14, 1L);
                ps.addBatch();
            }
//            List<List<Double>> position = AreaUtil.getPosition(map);
            List<Double> position = AreaUtil.getPosition(map);
            if (null != position) {
                ps.setLong(1, snow.nextId());
                ps.setString(2, subId);
                ps.setString(3, "position");
                ps.setString(4, "形状方位图");
                ps.setString(5, "list");
                ps.setString(6, position.toString());
                ps.setString(7, "");
                ps.setString(8, author);
                ps.setString(9, "WEB");
                ps.setLong(10, System.currentTimeMillis());
                ps.setLong(11 , 0L);
                ps.setLong(12 , 0L);
                ps.setLong(13 , 0L);
                ps.setLong(14, 1L);
                ps.addBatch();
            }
            ps.executeBatch();
            connection.commit();
            ps.close();
        } catch (SQLException throwables) {
            log.error(throwables.getMessage(), throwables);
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
            return "0";
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                log.error(throwables.getMessage(), throwables);
            }
        }
        return subId;
    }

    /**
     *@author zhangxuan
     *@Description TODO 插入主目标的其他信息
     *@Date 10:22 2021/1/7
     *@Param [sqliteUrl, targetElseData]
     *@return int
     **/
    public synchronized int addTargetElseList(String sqliteUrl, List<TargetElseData> targetElseData) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        PreparedStatement ps = connection.prepareStatement(ADD_TARGET_ELSE_SQL);
        try {
            for (TargetElseData data : targetElseData) {
                ps.setString(1, data.getId());
                ps.setString(2, data.getAchievementId());
                ps.setString(3, data.getKey());
                ps.setString(4, data.getKeyName());
                ps.setString(5, data.getKeyType());
                ps.setString(4, data.getKeyValue());
                ps.setString(7, data.getRemark());
                ps.setString(8, data.getPerson());
                ps.setString(9, data.getSource());
                ps.setLong(10, System.currentTimeMillis());
                ps.setLong(11 , 0L);
                ps.setLong(12 , 0L);
                ps.setLong(13 , 0L);
                ps.setLong(14, 1L);
                ps.addBatch();
            }
            return ps.executeBatch().length;
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 插入子目标信息
     *@Date 10:28 2021/1/7
     *@Param [sqliteUrl, targetSubData]
     *@return int
     **/
    public synchronized int addTargetSubList(String sqliteUrl, List<TargetSubData> targetSubData) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        PreparedStatement ps = connection.prepareStatement(ADD_TARGET_SUB_SQL);
        try {
            for (TargetSubData data : targetSubData) {
                ps.setString(1, data.getId());
                ps.setString(2, data.getChild_id());
                ps.setString(3, data.getKey());
                ps.setString(4, data.getKeyName());
                ps.setString(5, data.getKeyType());
                ps.setString(6, data.getKeyValue());
                ps.setString(7, data.getRemark());
                ps.setString(8, data.getPerson());
                ps.setString(9, data.getSource());
                ps.setLong(10, System.currentTimeMillis());
                ps.setLong(11 , 0L);
                ps.setLong(12 , 0L);
                ps.setLong(13 , 0L);
                ps.setLong(14, 1L);
                ps.addBatch();
            }
            return ps.executeBatch().length;
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询子目标列表
     *@Date 15:41 2021/1/12
     *@Param [sqliteUrl, name]
     *@return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    public synchronized List<Map<String, Object>> selectTargetSubList(String sqliteUrl, String webUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        List<Map<String, Object>> list = new ArrayList<>();
        List<Map<String, Object>> old = new ArrayList<>();
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            String subId = null;
            Map<String, Object> map = new HashMap<>();
            while (resultSet.next()) {
                if (null == subId) {
                    subId = resultSet.getString(2);
                } else if (!resultSet.getString(2).equals(subId)) {
                    if (null == map.get("icon")) {
                        map.put("icon", null);
                    }
                    if (null == map.get("targetClassify")) {
                        map.put("targetClassify", null);
                    }
                    map.put("subId", subId);
                    if (!map.get("zkwctype").toString().equals("true")) {
                        list.add(map);
                    } else {
                        old.add(map);
                    }
                    map = new HashMap<>();
                    subId = resultSet.getString(2);
                }
                switch (resultSet.getString(3)) {
                    case "targetName":
                        map.put("targetName", resultSet.getString(6));
                        break;
                    case "icon":
                        map.put("icon", webUrl + resultSet.getString(6));
                        break;
                    case "position":
                        map.put("position", FileElseUtil.getStringTransitionList(resultSet.getString(6)));
                    default:
                        break;
                }
                if (resultSet.getString(7).equals("pre")) {
                    map.put("zkwctype", 1);
                } else {
                    map.put("zkwctype", 0);
                }
            }
            if (null != subId) {
                if (null == map.get("icon")) {
                    map.put("icon", null);
                }
                if (null == map.get("targetClassify")) {
                    map.put("targetClassify", null);
                }
                map.put("subId", subId);
                if (!map.get("zkwctype").toString().equals("1")) {
                    list.add(map);
                } else {
                    old.add(map);
                }
            }
            list.addAll(old);
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
        return list;
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询子目标列表
     *@Date 15:41 2021/1/12
     *@Param [sqliteUrl, name]
     *@return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    public synchronized List<Map<String, Object>> selectTargetSubListExternal(String sqliteUrl, String webUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        List<Map<String, Object>> list = new ArrayList<>();
        List<Map<String, Object>> old = new ArrayList<>();
        PreparedStatement ps = connection.prepareStatement("SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_table WHERE data_state = '1' AND remark NOT IN ('pre') ORDER BY child_id,insert_time DESC");
        ResultSet resultSet = ps.executeQuery();
        try {
            String subId = null;
            Map<String, Object> map = new HashMap<>();
            while (resultSet.next()) {
                if (null == subId) {
                    subId = resultSet.getString(2);
                } else if (!resultSet.getString(2).equals(subId)) {
                    if (null == map.get("icon")) {
                        map.put("icon", null);
                    }
                    if (null == map.get("targetClassify")) {
                        map.put("targetClassify", null);
                    }
                    map.put("subId", subId);
                    if (!map.get("zkwctype").toString().equals("true")) {
                        list.add(map);
                    } else {
                        old.add(map);
                    }
                    map = new HashMap<>();
                    subId = resultSet.getString(2);
                }
                switch (resultSet.getString(3)) {
                    case "targetName":
                        map.put("targetName", resultSet.getString(6));
                        break;
                    case "icon":
                        map.put("icon", webUrl + resultSet.getString(6));
                        break;
                    case "position":
                        map.put("position", FileElseUtil.getStringTransitionList(resultSet.getString(6)));
                    default:
                        break;
                }
                if (resultSet.getString(7).equals("pre")) {
                    map.put("zkwctype", true);
                } else {
                    map.put("zkwctype", false);
                }
            }
            if (null != subId) {
                if (null == map.get("icon")) {
                    map.put("icon", null);
                }
                if (null == map.get("targetClassify")) {
                    map.put("targetClassify", null);
                }
                map.put("subId", subId);
                if (!map.get("zkwctype").toString().equals("true")) {
                    list.add(map);
                } else {
                    old.add(map);
                }
            }
            list.addAll(old);
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
        return list;
    }

    /**
     *@author zhangxuan
     *@Description TODO 插入目标与子目标关系
     *@Date 10:39 2021/1/7
     *@Param [sqliteUrl, targetSubRelation]
     *@return boolean
     **/
    public synchronized boolean addTargetSubRelation(String sqliteUrl, TargetSubRelation data) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return false;
        }
        PreparedStatement ps = connection.prepareStatement(ADD_TARGET_SUB_RELATION_SQL);
        try {
            ps.setLong(1, 1L);
            ps.setString(2, data.getTargetId());
            ps.setString(3, data.getChildName());
            ps.setLong(4, data.getChildNum() == null ? 0L : data.getChildNum());
            ps.setLong(5, data.getVersion() == null ? 0L : data.getVersion());
            ps.setString(6, data.getRemark());
            ps.execute();
            return true;
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 插入版本信息
     *@Date 10:50 2021/1/7
     *@Param [sqliteUrl, data]
     *@return boolean
     **/
    public synchronized boolean addVersion(String sqliteUrl, Version data) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return false;
        }
        PreparedStatement ps = connection.prepareStatement(ADD_VERION_SQL);
        try {
            ps.setLong(1, data.getId());
            ps.setLong(2, data.getTargetNumber());
            ps.setString(3, data.getVersion());
            ps.setString(4, data.getPublishNum());
            ps.setLong(5, System.currentTimeMillis());
            ps.setString(6, data.getAchievementType());
            ps.setString(7, data.getKeyword());
            ps.setString(8, data.getUnit());
            ps.setString(9 , data.getRemark());
            ps.setLong(10, System.currentTimeMillis());
            ps.setLong(11, 0L);
            ps.setLong(12, 1L);
            ps.execute();
            return true;
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 删除目标
     *@Date 14:37 2021/1/7
     *@Param [sqliteUrl]
     *@return int
     **/
    public synchronized int deleteTarget(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        int result = 0;
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SQL);
        ResultSet resultSet = ps.executeQuery();
        List<Long> list = new ArrayList<>();
        while (resultSet.next()){
            list.add(resultSet.getLong("id"));
        }
        resultSet.close();
        ps.close();
        if (!list.isEmpty()){
            ps = connection.prepareStatement(DELETE_TARGET_SQL);
            for (Long id: list) {
                ps.setLong(1, System.currentTimeMillis());
                ps.setLong(2, id);
                ps.addBatch();
            }
            result = ps.executeBatch().length;
        }
        ps.close();
        connection.close();
        return result;
    }

    /**
     *@author zhangxuan
     *@Description TODO 删除目标其他信息
     *@Date 14:55 2021/1/7
     *@Param [sqliteUrl]
     *@return int
     **/
    public synchronized int deleteTargetElse(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        int result = 0;
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
        ResultSet resultSet = ps.executeQuery();
        List<Long> list = new ArrayList<>();
        while (resultSet.next()){
            list.add(resultSet.getLong("id"));
        }
        resultSet.close();
        ps.close();
        if (!list.isEmpty()){
            ps = connection.prepareStatement(DELETE_TARGET_ELSE_SQL);
            for (Long id: list) {
                ps.setLong(1, System.currentTimeMillis());
                ps.setLong(2, id);
                ps.addBatch();
            }
            result = ps.executeBatch().length;
        }
        ps.close();
        connection.close();
        return result;
    }

    /**
     *@author zhangxuan
     *@Description TODO 删除子目标
     *@Date 15:03 2021/1/7
     *@Param [sqliteUrl, childId]
     *@return int
     **/
    public synchronized int deleteTargetSub(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        int result = 0;
        PreparedStatement ps = connection.prepareStatement(DELETE_TARGET_ALL_SUB_SQL);
        ps.setString(1, childId);
        result = ps.executeUpdate();
        ps.close();
        connection.close();
        return result;
    }

    /**
     *@author zhangxuan
     *@Description TODO 删除关系
     *@Date 15:25 2021/1/7
     *@Param [sqliteUrl]
     *@return int
     **/
    public synchronized int deleteTargetSubRelation(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        PreparedStatement ps = connection.prepareStatement(DELETE_TARGET_SUB_RELATION_SQL);
        try {
            return ps.executeUpdate();
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 删除版本信息
     *@Date 16:01 2021/1/7
     *@Param [sqliteUrl, id]
     *@return int
     **/
    public synchronized int deleteVersion(String sqliteUrl, long id) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        PreparedStatement ps = connection.prepareStatement(DELETE_VERSION_SQL);
        try {
            ps.setLong(1, System.currentTimeMillis());
            ps.setLong(2, id);
            return ps.executeUpdate();
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 修改目标与子目标字段
     *@Date 16:01 2021/1/7
     *@Param [sqliteUrl, id]
     *@return int
     **/
    public synchronized int updateTableKeyAndKeyValue(String sqliteUrl, String tarId, String subId, String key, String value) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return 0;
        }
        PreparedStatement ps;
        if (StringUtils.isBlank(subId)) {
            ps = connection.prepareStatement(UPDATE_MAIN_KEY_VALUE_BY_KEY);
        } else {
            ps = connection.prepareStatement(UPDATE_SUB_KEY_VALUE_BY_KEY);
        }
        try {
            ps.setString(1, value);
            ps.setString(2, key);
            if (StringUtils.isNotBlank(subId)) {
                ps.setString(3, subId);
            }
            return ps.executeUpdate();
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询目标信息
     *@Date 16:19 2021/1/7
     *@Param [sqliteUrl]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetData>
     **/
    public synchronized List<TargetData> selectTargetData(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new ArrayList<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<TargetData> list = new ArrayList<>();
            while (resultSet.next()) {
                TargetData targetData = new TargetData();
                targetData.setId(resultSet.getString(1));
                targetData.setKey(resultSet.getString(2));
                targetData.setKeyName(resultSet.getString(3));
                targetData.setKeyType(resultSet.getString(4));
                targetData.setKeyValue(resultSet.getString(5));
                targetData.setRemark(resultSet.getString(6));
                targetData.setPerson(resultSet.getString(7));
                targetData.setSource(resultSet.getString(8));
                list.add(targetData);
            }
            return list;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询目标基本信息
     * @return
     */
    public static Map<String, Object> selectTargetBase(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            List<Object> data = null;
            while (resultSet.next()){
                data = new ArrayList<>();
                data.add(resultSet.getObject(5));
                data.add(resultSet.getObject(6));
                resultMap.put(resultSet.getString(2), data);
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询目标详细信息
     * @return
     */
    public static Map<String, Object> selectTargetDetail(String sqliteUrl, String taskId) throws SQLException, ClassNotFoundException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_AUTHOR_SQL);
        ps.setString(1, taskId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()){
                if (resultSet.getString("key_type").toLowerCase().contains("double")) {
                    resultMap.put(resultSet.getString("key"), FileElseUtil.getStringTransitionDouble(resultSet.getString("key_value")));

                } else {
                    resultMap.put(resultSet.getString("key"), resultSet.getString("key_value"));
                }
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询目标详细信息
     * @return
     */
    public static Map<String, Object> selectTargetSpecial(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            List<Object> data = null;
            while (resultSet.next()){
                data = new ArrayList<>();
                data.add(resultSet.getObject(6));
                data.add(resultSet.getObject(7));
                resultMap.put(resultSet.getString(3), data);
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询目标专有信息
     * @return
     */
    public static Map<String, Object> selectTargetFunction(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_FUNCTION_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()){
                resultMap.put(resultSet.getString(3), resultSet.getString(6));
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询目标其他信息
     *@Date 16:33 2021/1/7
     *@Param [sqliteUrl]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetElseData>
     **/
    public synchronized List<TargetElseData> selectTargetElse(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new ArrayList<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<TargetElseData> list = new ArrayList<>();
            while (resultSet.next()) {
                TargetElseData targetElseData = new TargetElseData();
                targetElseData.setId(resultSet.getString(1));
                targetElseData.setAchievementId(resultSet.getString(2));
                targetElseData.setKey(resultSet.getString(3));
                targetElseData.setKeyName(resultSet.getString(4));
                targetElseData.setKeyType(resultSet.getString(5));
                targetElseData.setKeyValue(resultSet.getString(6));
                targetElseData.setRemark(resultSet.getString(7));
                targetElseData.setPerson(resultSet.getString(8));
                targetElseData.setSource(resultSet.getString(9));
                list.add(targetElseData);
            }
            return list;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询目标其他信息
     *@Date 16:33 2021/1/7
     *@Param [sqliteUrl]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetElseData>
     **/
    public synchronized List<TargetElseData> selectTargetElseByPerson(String sqliteUrl,String taskId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new ArrayList<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
        ps.setString(1, taskId);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<TargetElseData> list = new ArrayList<>();
            while (resultSet.next()) {
                TargetElseData targetElseData = new TargetElseData();
                targetElseData.setId(resultSet.getString(1));
                targetElseData.setAchievementId(resultSet.getString(2));
                targetElseData.setKey(resultSet.getString(3));
                targetElseData.setKeyName(resultSet.getString(4));
                targetElseData.setKeyType(resultSet.getString(5));
                targetElseData.setKeyValue(resultSet.getString(6));
                targetElseData.setRemark(resultSet.getString(7));
                targetElseData.setPerson(resultSet.getString(8));
                targetElseData.setSource(resultSet.getString(9));
                list.add(targetElseData);
            }
            return list;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询单个子目标
     *@Date 16:39 2021/1/7
     *@Param [sqliteUrl, childId]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetSubData>
     **/
    public synchronized Map<String, Object> selectTargetSub(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_SINGLE_SQL + " AND key NOT IN ('icon')");
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            List<Object> data = null;
            while (resultSet.next()) {
                data = new ArrayList<>();
                data.add(resultSet.getObject(6));
                data.add(resultSet.getObject(7));
                resultMap.put(resultSet.getString(3), data);
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询单个子目标
     *@Date 16:39 2021/1/7
     *@Param [sqliteUrl, childId]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetSubData>
     **/
    public synchronized Map<String, Object> selectTargetSubValue(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_SINGLE_SQL + " AND key NOT IN ('icon')");
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()) {
                resultMap.put(resultSet.getString(3), resultSet.getObject(6));
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询单个子目标
     *@Date 16:39 2021/1/7
     *@Param [sqliteUrl, childId]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetSubData>
     **/
    public synchronized Map<String, Object> selectTargetSubName(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_SINGLE_SQL);
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()) {
                if (resultSet.getString(3).equals("icon")) {
                    continue;
                }
                if (ObjectUtil.isNotEmpty(resultSet.getString(6))) {
                    resultMap.put(resultSet.getString(4),  resultSet.getString(6) + resultSet.getString(7));
                }
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询子目标信息
     *@Date 17:20 2021/1/7
     *@Param [sqliteUrl]
     *@return java.util.Map<java.lang.String,java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.TargetSubData>>
     **/
    public synchronized Map<String, List<TargetSubData>> selectTargetSub(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_NO_PRE_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, List<TargetSubData>> map = new LinkedHashMap<>();
            String id = null;
            String nextId = null;
            List<TargetSubData> list = new ArrayList<>();
            while (resultSet.next()) {
                if (id == null){
                    id = String.valueOf(resultSet.getLong(2));
                }
                nextId = String.valueOf(resultSet.getLong(2));
                if (nextId != null && !id.equals(nextId)){
                    map.put(id, list);
                    list = new ArrayList<>();
                }
                TargetSubData targetSubData = new TargetSubData();
                targetSubData.setId(resultSet.getString(1));
                targetSubData.setChild_id(resultSet.getString(2));
                targetSubData.setKey(resultSet.getString(3));
                targetSubData.setKeyName(resultSet.getString(4));
                targetSubData.setKeyType(resultSet.getString(5));
                targetSubData.setKeyValue(resultSet.getString(6));
                targetSubData.setRemark(resultSet.getString(7));
                targetSubData.setPerson(resultSet.getString(8));
                targetSubData.setSource(resultSet.getString(9));
                list.add(targetSubData);
                id = nextId;
            }
            return map;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询目标与子目标关系
     *@Date 18:00 2021/1/7
     *@Param [sqliteUrl]
     *@return cn.xinchuang.archetypetargetdata.sqlite.entity.TargetSubRelation
     **/
    public synchronized TargetSubRelation selectTargetSubRelation(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_RELATION_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            if (resultSet.next()) {
                TargetSubRelation targetSubRelation = new TargetSubRelation();
                targetSubRelation.setId(resultSet.getString(1));
                targetSubRelation.setTargetId(resultSet.getString(2));
                targetSubRelation.setChildName(resultSet.getString(3));
                targetSubRelation.setChildNum(resultSet.getLong(4));
                targetSubRelation.setVersion(resultSet.getLong(5));
                targetSubRelation.setRemark(resultSet.getString(6));
                return targetSubRelation;
            }
            return null;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询版本号
     *@Date 18:04 2021/1/7
     *@Param [sqliteUrl]
     *@return java.util.List<cn.xinchuang.archetypetargetdata.sqlite.entity.Version>
     **/
    public synchronized List<Version> selectVersion(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_VERSION_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<Version> list = new ArrayList<>();
            while (resultSet.next()) {
                Version version = new Version();
                version.setId(resultSet.getLong(1));
                version.setTargetNumber(resultSet.getLong(2));
                version.setVersion(resultSet.getString(3));
                version.setPublishNum(resultSet.getString(4));
                version.setMadeTime(resultSet.getLong(5));
                version.setAchievementType(resultSet.getString(6));
                version.setKeyword(resultSet.getString(7));
                version.setUnit(resultSet.getString(8));
                version.setRemark(resultSet.getString(9));
                list.add(version);
            }
            return list;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询目标信息
     *@Date 16:31 2021/1/14
     *@Param [sqliteUrl, data]
     *@return java.util.Map<java.lang.String,java.lang.Object>
     **/
    public synchronized Map<String, Object> selectTargetData(String sqliteUrl, List<String> data) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        Map<String, Object> resultMap = new HashMap<>();
        String sql = "SELECT id,key,key_name,key_type,key_value,remark,person,source FROM target_table WHERE data_state = '1' and key in (?)";
        String sqlIn = data.stream().map(x -> "'" + x + "'")
                .collect(Collectors.joining(",","(",")"));
        sql = sql.replace("(?)", sqlIn);
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        try {
            List<String> elseList = new ArrayList<>();
            while (resultSet.next()) {
                for (String datum : data) {
                    if (datum.equals(resultSet.getString(2))) {
                        if (resultSet.getString(2).equals("targetKeywords")) {
                            resultMap.put(resultSet.getString(2), FileElseUtil.getStringTransitionList(resultSet.getString(5)));
                        } else {
                            resultMap.put(resultSet.getString(2), resultSet.getString(5));
                        }
                        elseList.add(datum);
                        break;
                    }
                }
            }
            if (data.size() == elseList.size()) {
                data = null;
            } else {
                data.removeAll(elseList);
            }
            if (CollectionUtil.isNotEmpty(data)) {
                for (String datum : data) {
                    if (datum.equals("targetKeywords")) {
                        resultMap.put(datum, new ArrayList<>());
                    } else {
                        resultMap.put(datum, null);
                    }
                }
            }
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
        return resultMap;
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询目标其他信息
     *@Date 16:31 2021/1/14
     *@Param [sqliteUrl, data]
     *@return java.util.Map<java.lang.String,java.lang.Object>
     **/
    public synchronized Map<String, Object> selectTargetElseData(String sqliteUrl, List<String> data) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        Map<String, Object> resultMap = new HashMap<>();
        String sql = "SELECT id,key,key_name,key_type,key_value,remark,person,source FROM target_else_table WHERE data_state = '1' and key in (?)";
        String sqlIn = data.stream().map(x -> "'" + x + "'")
                .collect(Collectors.joining(",","(",")"));
        sql = sql.replace("(?)", sqlIn);
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        try {
            while (resultSet.next()) {
                for (String datum : data) {
                    if (datum.equals(resultSet.getString(2))) {
                        resultMap.put(resultSet.getString(2), resultSet.getString(5));
                        break;
                    }
                }
            }
            for (String datum : data) {
                if (ObjectUtil.isEmpty(resultMap.get(datum))) {
                    resultMap.put(datum, null);
                }
            }
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
        return resultMap;
    }

    /**
     *@author zhangxuan
     *@Description TODO 查询子目标信息
     *@Date 17:44 2021/1/18
     *@Param [sqliteUrl, data]
     *@return java.util.Map<java.lang.String,java.lang.Object>
     **/
    public synchronized List<Map<String, Object>> selectTargetSubData(String sqliteUrl, List<String> data) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        String sum = "SELECT COUNT(1) FROM (SELECT child_id,COUNT(*) FROM target_sub_table WHERE remark NOT IN ('pre') GROUP BY child_id)";
        int all = 0;
        PreparedStatement ps = connection.prepareStatement(sum);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            all = resultSet.getInt(1);
        }
        if (all == 0) {
            return resultList;
        }
        resultSet.close();
        ps.close();
        Map<String, Object> resultMap = new HashMap<>();
        String sql = "SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_table WHERE data_state = '1' AND key in (?) AND remark NOT IN ('pre') order by child_id";
        String sqlIn = data.stream().map(x -> "'" + x + "'")
                .collect(Collectors.joining(",","(",")"));
        sql = sql.replace("(?)", sqlIn);
        ps = connection.prepareStatement(sql);
        resultSet = ps.executeQuery();
        try {
            Map<String, Object> map = null;
            long sudId = 0L;
            Long nextId = null;
            while (resultSet.next()) {
                sudId = resultSet.getLong(2);
                if (null == nextId) {
                    nextId = resultSet.getLong(2);
                }
                if (null == map) {
                    map = new HashMap<>();
                    map.put("subId", sudId + "");
                    for (String datum : data) {
                        map.put(datum, null);
                    }
                }else if (nextId != sudId) {
                    resultList.add(map);
                    map = new HashMap<>();
                    map.put("subId", sudId + "");
                    for (String datum : data) {
                        map.put(datum, null);
                    }
                }
                String key = resultSet.getString(3);
                if (null != map) {
                    for (String s : map.keySet()) {
                        if (s.equals(key)) {
                            map.put(s, resultSet.getString(6) + (StringUtils.isNotBlank(resultSet.getString(7)) ? resultSet.getString(7) : ""));
                            break;
                        }
                    }
                }
                nextId = sudId;
            }
            if (null != map) {
                resultList.add(map);
            }
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
        return resultList;
    }

    /**
     *@author zhangxuan
     *@Description TODO 计算子目标形状
     *@Date 9:19 2021/1/28
     *@Param [sqliteUrl]
     *@return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    public synchronized List<Map<String, Object>> getSubPosition(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return null;
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        String sum = "SELECT child_id,COUNT(*) FROM target_sub_table WHERE remark NOT IN ('pre') GROUP BY child_id";
        PreparedStatement ps = connection.prepareStatement(sum);
        ResultSet resultSet = ps.executeQuery();
        List<Long> childList = new ArrayList<>();
        while (resultSet.next()) {
            childList.add(resultSet.getLong(1));
        }
        resultSet.close();
        ps.close();
        if (childList.isEmpty()) {
            return resultList;
        }
        for (Long aLong : childList) {
            Map<String, Object> map = AreaUtil.getSubResultMap();
            String sql = "SELECT key,key_value FROM target_sub_table WHERE key IN ('targetName','position','height','area','plotinfoobj','targetClassify') AND child_id = ? ";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, aLong);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                map.put(resultSet.getString(1), resultSet.getObject(2));
            }
            map.put("subId", aLong);
            resultList.add(map);
            resultSet.close();
            ps.close();
        }
        connection.close();
        return resultList;
    }

    public synchronized int targetSubDetailMaintain(String sqliteUrl, List<TargetData> list, String subId, String username) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return 0;
        }
        //1.查询是否已有子目标详细信息
        if(!subId.equals("0")) {
            //2.删除原有的子目标详细信息
            PreparedStatement ps = connection.prepareStatement(DELETE_SUB_TARGET_ELSE_SQL);
            ps.setString(1, subId);
            ps.executeUpdate();
        }
        //3.保存新的子目标详细信息
        PreparedStatement ps = connection.prepareStatement(ADD_SUB_TARGET_ELSE_SQL);
        try {
            for (TargetData data : list) {
                ps.setLong(1, snow.nextId());
                ps.setString(2, subId);
                ps.setString(3, data.getKey());
                ps.setString(4, data.getKeyName());
                ps.setString(5, data.getKeyType());
                ps.setString(6, data.getKeyValue());
                ps.setString(7, data.getRemark());
                ps.setString(8, username);
                ps.setString(9, "WEB");
                ps.setLong(10, System.currentTimeMillis());
                ps.setLong(11, 0L);
                ps.setLong(12, 0L);
                ps.setLong(13, 0L);
                ps.setLong(14, 1L);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();
            return 1;
        } finally {
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询单个子目标详细信息
     * @param sqliteUrl
     * @param childId
     * @return
     * @throws SQLException
     */
    public synchronized Map<String, Object> selectTargetSubElse(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_ELSE_SINGLE_SQL);
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            List<Object> data = null;
            while (resultSet.next()) {
                data = new ArrayList<>();
                data.add(resultSet.getObject(6));
                data.add(resultSet.getObject(7));
                resultMap.put(resultSet.getString(3), data);
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询单个子目标详细信息
     * @param sqliteUrl
     * @param childId
     * @return
     * @throws SQLException
     */
    public synchronized Map<String, Object> selectTargetSubElseValue(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_ELSE_SINGLE_SQL);
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()) {
                resultMap.put(resultSet.getString(3), resultSet.getString(6));
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 查询单个子目标详细信息
     * @param sqliteUrl
     * @param childId
     * @return
     * @throws SQLException
     */
    public synchronized Map<String, Object> selectTargetSubElseName(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_ELSE_SINGLE_SQL);
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()) {
                if (ObjectUtil.isNotEmpty(resultSet.getString(6))) {
                    resultMap.put(resultSet.getString(4), resultSet.getString(6) + resultSet.getString(7));
                }
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    /**
     * 上传子缩略图
     * @param sqliteUrl
     * @param subId
     * @param iconUrl
     * @param userId
     * @throws SQLException
     */
    public void uploadSubIcon(String sqliteUrl, String subId, String iconUrl, String userId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return;
        }
        String update = "SELECT id,child_id,key,key_name,key_type,key_value,remark,person,source FROM target_sub_table WHERE data_state = '1' AND child_id = ? AND key = 'icon'";
        PreparedStatement ps = connection.prepareStatement(update);
        ps.setString(1, subId);
        ResultSet rs = ps.executeQuery();
        try {
            String id = null;
            while (rs.next()) {
                id = rs.getString(1);
                if (StringUtils.isNotBlank(rs.getString(9))) {
                    FileUtil.del(rs.getString(9));
                }
            }
            if (null != id) {
                String delete = "DELETE FROM target_sub_table WHERE id = ?";
                ps = connection.prepareStatement(delete);
                ps.setString(1, id);
                ps.execute();
            }
            ps = connection.prepareStatement(ADD_TARGET_SUB_SQL);
            ps.setLong(1, snow.nextId());
            ps.setString(2, subId);
            ps.setString(3, "icon");
            ps.setString(4, "展示图");
            ps.setString(5, "picture");
            ps.setString(6, iconUrl);
            ps.setString(7, "展示图");
            ps.setString(8, userId);
            ps.setString(9, iconUrl);
            ps.setLong(10, System.currentTimeMillis());
            ps.setLong(11, 0L);
            ps.setLong(12, 0L);
            ps.setLong(13, 0L);
            ps.setLong(14, 1L);
            ps.executeUpdate();
        } finally {
            rs.close();
            ps.close();
            connection.close();
        }
    }

    public int selectSubCount(String sqliteUrl, String subId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection) {
            return 0;
        }
        int count = 0;
        PreparedStatement ps = connection.prepareStatement(SELECT_SUB_COUNT_SQL);
        ps.setString(1, subId);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        ps.close();
        connection.close();
        return count;
    }

    public static Map<String, Object> selectTargetAll(String sqliteUrl) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SQL);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()){
                if (resultSet.getString(4).toLowerCase().contains("double")) {
                    resultMap.put(resultSet.getString(2),
                            FileElseUtil.getStringTransitionDouble(resultSet.getString(5)) + (StringUtils.isNotBlank(resultSet.getString(6)) ? resultSet.getString(6) : "") );
                } else {
                    resultMap.put(resultSet.getString(2), resultSet.getString(5) + (StringUtils.isNotBlank(resultSet.getString(6)) ? resultSet.getString(6) : ""));
                }
            }
            ps = connection.prepareStatement(SELECT_TARGET_ELSE_SQL);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                if (resultSet.getString(5).toLowerCase().contains("double")) {
                    resultMap.put(resultSet.getString(3),
                            FileElseUtil.getStringTransitionDouble(resultSet.getString(6)) + (StringUtils.isNotBlank(resultSet.getString(7)) ? resultSet.getString(7) : ""));
                } else {
                    resultMap.put(resultSet.getString(3), resultSet.getString(6) + (StringUtils.isNotBlank(resultSet.getString(7)) ? resultSet.getString(7) : ""));
                }
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

    public static Map<String, Object> selectTargetSubAll(String sqliteUrl, String childId) throws SQLException {
        DbConnManager dbConnManager = new DbConnManager();
        Connection connection = dbConnManager.getConn(sqliteUrl);
        if (null == connection){
            return new HashMap<>();
        }
        PreparedStatement ps = connection.prepareStatement(SELECT_TARGET_SUB_SINGLE_SQL);
        ps.setString(1, childId);
        ResultSet resultSet = ps.executeQuery();
        try {
            Map<String, Object> resultMap = new HashMap<>();
            while (resultSet.next()) {
                if (StringUtils.isNotBlank(resultSet.getString(5))) {
                    if (resultSet.getString(5).toLowerCase().contains("double")) {
                        resultMap.put(resultSet.getString(3),
                                FileElseUtil.getStringTransitionDouble(resultSet.getString(6)));
                    } else {
                        resultMap.put(resultSet.getString(3), resultSet.getString(6));
                    }
                }
            }
            ps = connection.prepareStatement(SELECT_TARGET_SUB_ELSE_SINGLE_SQL);
            ps.setString(1, childId);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                if (StringUtils.isNotBlank(resultSet.getString(5))) {
                    if (resultSet.getString(5).toLowerCase().contains("double")) {
                        resultMap.put(resultSet.getString(3),
                                FileElseUtil.getStringTransitionDouble(resultSet.getString(6)));
                    } else {
                        resultMap.put(resultSet.getString(3), resultSet.getString(6));
                    }
                }
            }
            return resultMap;
        } finally {
            resultSet.close();
            ps.close();
            connection.close();
        }
    }

}